﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using System.Data.SqlClient;
using Entities;

namespace DataAccessLayer
{
    public class TheHocVienDAL : DBConnection
    {
        public List<TheHocVienEntity> getAll()
        {
            List<TheHocVienEntity> lst = new List<TheHocVienEntity>();
            try
            {
                MySqlCommand cmd = new MySqlCommand("the_hoc_vien_getAll", con);
                cmd.CommandType = CommandType.StoredProcedure;

                if (con.State != ConnectionState.Open)
                    con.Open();
                MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                while (dr.Read())
                {
                    TheHocVienEntity obj = new TheHocVienEntity();
                    obj.MaThe = Convert.ToInt32(dr["MaThe"]);
                    obj.MaHocVien = Convert.ToString(dr["MaHocVien"]);
                    obj.MaKhoaHoc = Convert.ToInt32(dr["MaKhoaHoc"]);
                    obj.MaGoiKhoa = Convert.ToInt32(dr["MaGoiKhoa"]);
                    obj.DkCap = Convert.ToBoolean(dr["DkCap"]);
                    obj.NgayDangKy = Convert.ToDateTime(dr["NgayDangKy"]);
                    obj.NgayHetHan = Convert.ToDateTime(dr["NgayHetHan"]);
                    obj.SoBuoi = Convert.ToInt32(dr["SoBuoi"]);
                    obj.CoThe = Convert.ToBoolean(dr["CoThe"]);
                    lst.Add(obj);
                }
                dr.Close();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return lst;
        }

        public TheHocVienEntity getById(int _id)
        {
            TheHocVienEntity obj = new TheHocVienEntity();
            try
            {
                MySqlCommand cmd = new MySqlCommand("the_hoc_vien_getById", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("maThe", _id);

                if (con.State != ConnectionState.Open)
                    con.Open();
                MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                if (dr.Read())
                {
                    obj.MaThe = Convert.ToInt32(dr["MaThe"]);
                    obj.MaHocVien = Convert.ToString(dr["MaHocVien"]);
                    obj.MaKhoaHoc = Convert.ToInt32(dr["MaKhoaHoc"]);
                    obj.MaGoiKhoa = Convert.ToInt32(dr["MaGoiKhoa"]);
                    obj.DkCap = Convert.ToBoolean(dr["DkCap"]);
                    obj.NgayDangKy = Convert.ToDateTime(dr["NgayDangKy"]);
                    obj.NgayHetHan = Convert.ToDateTime(dr["NgayHetHan"]);
                    obj.SoBuoi = Convert.ToInt32(dr["SoBuoi"]);
                    obj.CoThe = Convert.ToBoolean(dr["CoThe"]);
                }
                dr.Close();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return obj;
        }

        public List<TheHocVienEntity> getByHocVienId(int _id)
        {
            List<TheHocVienEntity> lst = new List<TheHocVienEntity>();
            try
            {
                MySqlCommand cmd = new MySqlCommand("the_hoc_vien_getByHocVienId", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("maHocVien", _id);

                if (con.State != ConnectionState.Open)
                    con.Open();
                MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                while (dr.Read())
                {
                    TheHocVienEntity obj = new TheHocVienEntity();
                    obj.MaThe = Convert.ToInt32(dr["MaThe"]);
                    obj.MaHocVien = Convert.ToString(dr["MaHocVien"]);
                    obj.MaKhoaHoc = Convert.ToInt32(dr["MaKhoaHoc"]);
                    obj.MaGoiKhoa = Convert.ToInt32(dr["MaGoiKhoa"]);
                    obj.DkCap = Convert.ToBoolean(dr["DkCap"]);
                    obj.NgayDangKy = Convert.ToDateTime(dr["NgayDangKy"]);
                    obj.NgayHetHan = Convert.ToDateTime(dr["NgayHetHan"]);
                    obj.SoBuoi = Convert.ToInt32(dr["SoBuoi"]);
                    obj.CoThe = Convert.ToBoolean(dr["CoThe"]);
                    lst.Add(obj);
                }
                dr.Close();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return lst;
        }

        public TheHocVienEntity getByHocVien_KhoaHoc(string _maHocVien, int _maKhoaHoc)
        {
            TheHocVienEntity obj = new TheHocVienEntity();
            try
            {
                MySqlCommand cmd = new MySqlCommand("the_hoc_vien_getByHocVien_KhoaHoc", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("maHocVien", _maHocVien);
                cmd.Parameters.AddWithValue("maKhoaHoc", _maKhoaHoc);

                if (con.State != ConnectionState.Open)
                    con.Open();
                MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                if (dr.Read())
                {
                    obj.MaThe = Convert.ToInt32(dr["MaThe"]);
                    obj.MaHocVien = Convert.ToString(dr["MaHocVien"]);
                    obj.MaKhoaHoc = Convert.ToInt32(dr["MaKhoaHoc"]);
                    obj.MaGoiKhoa = Convert.ToInt32(dr["MaGoiKhoa"]);
                    obj.DkCap = Convert.ToBoolean(dr["DkCap"]);
                    obj.NgayDangKy = Convert.ToDateTime(dr["NgayDangKy"]);
                    obj.NgayHetHan = Convert.ToDateTime(dr["NgayHetHan"]);
                    obj.SoBuoi = Convert.ToInt32(dr["SoBuoi"]);
                    obj.CoThe = Convert.ToBoolean(dr["CoThe"]);
                }
                dr.Close();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return obj;
        }

        public List<TheHocVienEntity> getByKhoaHocId(int _id)
        {
            List<TheHocVienEntity> lst = new List<TheHocVienEntity>();
            try
            {
                MySqlCommand cmd = new MySqlCommand("the_hoc_vien_getByKhoaHocId", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("maKhoaHoc", _id);

                if (con.State != ConnectionState.Open)
                    con.Open();
                MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                while (dr.Read())
                {
                    TheHocVienEntity obj = new TheHocVienEntity();
                    obj.MaThe = Convert.ToInt32(dr["MaThe"]);
                    obj.MaHocVien = Convert.ToString(dr["MaHocVien"]);
                    obj.MaKhoaHoc = Convert.ToInt32(dr["MaKhoaHoc"]);
                    obj.MaGoiKhoa = Convert.ToInt32(dr["MaGoiKhoa"]);
                    obj.DkCap = Convert.ToBoolean(dr["DkCap"]);
                    obj.NgayDangKy = Convert.ToDateTime(dr["NgayDangKy"]);
                    obj.NgayHetHan = Convert.ToDateTime(dr["NgayHetHan"]);
                    obj.SoBuoi = Convert.ToInt32(dr["SoBuoi"]);
                    obj.CoThe = Convert.ToBoolean(dr["CoThe"]);
                    lst.Add(obj);
                }
                dr.Close();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return lst;
        }

        public int addTheHocVien(TheHocVienEntity _obj)
        {
            int result = 0;
            try
            {
                if (_obj.MaThe != -1)
                    throw new Exception("The Da Ton Tai");

                MySqlCommand cmd = new MySqlCommand("the_hoc_vien_insert", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("maHocVien", _obj.MaHocVien);
                cmd.Parameters.AddWithValue("maKhoaHoc", _obj.MaKhoaHoc);
                cmd.Parameters.AddWithValue("maGoiKhoa", _obj.MaGoiKhoa);
                cmd.Parameters.AddWithValue("dkCap", Convert.ToByte(_obj.DkCap));
                cmd.Parameters.AddWithValue("ngayDangKy", _obj.NgayDangKy);
                cmd.Parameters.AddWithValue("ngayHetHan", _obj.NgayHetHan);
                cmd.Parameters.AddWithValue("soBuoi", _obj.SoBuoi);
                cmd.Parameters.AddWithValue("coThe", Convert.ToByte(_obj.CoThe));
                cmd.Parameters.AddWithValue("lastId", MySqlDbType.Int32);
                cmd.Parameters["lastId"].Direction = ParameterDirection.Output;

                if (con.State != ConnectionState.Open)
                    con.Open();
                result = cmd.ExecuteNonQuery();
                int i = Convert.ToInt32(cmd.Parameters["lastId"].Value);
                if (_obj.MaThe == -1)
                    _obj.MaThe = i;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return result;
        }

        public int deleteTheHocVien(int _id)
        {
            int result = 0;
            try
            {
                if (_id == -1)
                    return 0;
                MySqlCommand cmd = new MySqlCommand("the_hoc_vien_delete", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("maThe", _id);
                cmd.Parameters["maThe"].Direction = ParameterDirection.Input;

                if (con.State != ConnectionState.Open)
                    con.Open();
                result = cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return result;
        }

        public int updateTheHocVien(TheHocVienEntity _obj)
        {
            int result = 0;
            try
            {
                if (_obj.MaThe == -1)
                    return addTheHocVien(_obj);

                MySqlCommand cmd = new MySqlCommand("the_hoc_vien_update", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("maThe", _obj.MaThe);
                cmd.Parameters.AddWithValue("maHocVien", _obj.MaHocVien);
                cmd.Parameters.AddWithValue("maKhoaHoc", _obj.MaKhoaHoc);
                cmd.Parameters.AddWithValue("maGoiKhoa", _obj.MaGoiKhoa);
                cmd.Parameters.AddWithValue("dkCap", Convert.ToByte(_obj.DkCap));
                cmd.Parameters.AddWithValue("ngayDangKy", _obj.NgayDangKy);
                cmd.Parameters.AddWithValue("ngayHetHan", _obj.NgayHetHan);
                cmd.Parameters.AddWithValue("soBuoi", _obj.SoBuoi);
                cmd.Parameters.AddWithValue("coThe", Convert.ToByte(_obj.CoThe));

                if (con.State != ConnectionState.Open)
                    con.Open();
                result = cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return result;
        }
    }
}
